﻿using System.Text;
using System.Data;

namespace CardLogistics.DAL
{
    /// <summary>
    /// 卡流水表
    /// </summary>
    public class CardRecordData
    {
        /// <summary>
        /// 根据半成品/成品入库单号获取制卡坏卡信息
        /// </summary>
        /// <param name="bcprkID">半成品/成品入库单号</param>
        /// <returns></returns>
        public DataTable GetZKHKByRKID(string bcprkID)
        {
            StringBuilder sbCard = new StringBuilder();
            sbCard.Append(" select t.cardno STARTCARDNO,t.cardno ENDCARDNO, 1 CARDAMOUNT from cardrecord t   ");
            sbCard.Append(" where t.bkchukusj is not null and t.bcprukusj is null and ");
            sbCard.Append("BKCHUKUID=(select OUTSTORAGEID from PUTINCARD where ID='" + bcprkID + "')");
            DataTable dt = DbHelperSQL.GetQueryResult(sbCard.ToString());
            return dt;
        }
        /// <summary>
        /// 根据开始卡号、结束卡号判断是否在出库单中
        /// </summary>
        /// <param name="bkckID">白卡出库ID</param>
        /// <param name="startNO">开始卡号</param>
        /// <param name="endNO">结束卡号</param>
        /// <returns>在出库单里返回true 否则返回false</returns>
        public DataTable ISBKCK(string bkckID,string startNO, string endNO)
        {
            StringBuilder sbCard = new StringBuilder();
            sbCard.Append(" select cardno from cardrecord where  bkchukuid='" + bkckID + "' and cardno>='" + startNO + "' and cardno<='" + endNO + "'");
            DataTable dt = DbHelperSQL.GetQueryResult(sbCard.ToString());
            return dt;
        }
        /// <summary>
        /// 根据开始卡号、结束卡号判断是否在卡片验收入库单中已存在
        /// </summary>
        /// <param name="strRuKuID">验收入库单ID</param>
        /// <param name="startNO">开始卡号</param>
        /// <param name="endNO">结束卡号</param>
        /// <returns>已存在返回true 否则返回false</returns>
        public bool IsExisRuKu(string strRuKuID, string startNO, string endNO)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) from storage_detailed_card");
            strSql.Append(" where ((to_number(" + startNO + ") between to_number(StartNo) and to_number(EndNo))");
            strSql.Append(" or (to_number(" + endNO + ") between to_number(StartNo) and to_number(EndNo))");
            strSql.Append(" or (to_number(StartNo) between to_number(" + startNO + ") and to_number(" + endNO + ")))");
            strSql.Append(" and CheckId not in (select ID from storage_check_card where Issubmit=4)");//过滤审批未通过的卡片验收入库信息
            if (strRuKuID != "")
            {
                strSql.Append(" and CheckId!='" + strRuKuID + "'");
            }
            DataTable dt = DbHelperSQL.GetQueryResult(strSql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                if (int.Parse(dt.Rows[0][0].ToString()) > 0)
                    return true;
            }
            return false;
        }
        /// <summary>
        /// 根据箱号、盒号判断是否在卡片验收入库单中已存在
        /// </summary>
        /// <param name="strRuKuID">验收入库单ID</param>
        /// <param name="startNO">箱号</param>
        /// <param name="endNO">盒号</param>
        /// <returns>箱号已存在返回“箱号”，盒号存在返回“盒号”，箱号盒号都存在则返回“箱号 盒号”，都不存在则返回“”</returns>
        public string IsExisBoxCardNORuKu(string strRuKuID, string boxNO, string cardNO)
        {
            string strResult = "";//返回结果
            //查询箱号是否已存在
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select count(0) from storage_detailed_card");
            //strSql.Append(" where BoxNO=" + boxNO + "");
            //strSql.Append(" and CheckId not in (select ID from storage_check_card where Issubmit=4)");//过滤审批未通过的卡片验收入库信息
            //if (strRuKuID != "")
            //{
            //    strSql.Append(" and CheckId!='" + strRuKuID + "'");
            //}
            //DataTable dt = DbHelperSQL.GetQueryResult(strSql.ToString());
            //if (dt != null && dt.Rows.Count > 0)
            //{
            //    if (int.Parse(dt.Rows[0][0].ToString()) > 0)
            //        strResult += "箱号";
            //}
            //查询盒号是否已存在
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(0) from storage_detailed_card");
            strSql.Append(" where CardNo=" + cardNO + "");
            strSql.Append(" and CheckId not in (select ID from storage_check_card where Issubmit=4)");//过滤审批未通过的卡片验收入库信息
            if (strRuKuID != "")
            {
                strSql.Append(" and CheckId!='" + strRuKuID + "'");
            }
            DataTable dt = DbHelperSQL.GetQueryResult(strSql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                if (int.Parse(dt.Rows[0][0].ToString()) > 0)
                    strResult += " 盒号";
            }
            return strResult;
        }
        /// <summary>
        /// 根据订单ID、明细中的箱号、开始卡号、结束卡号判断验收入库单明细是否符合卡片订货单标准
        /// </summary>
        /// <param name="strOrderId">卡片订货单ID</param>
        /// <param name="boxNO">验收入库箱号</param>
        /// <param name="startNO">验收入库开始卡号</param>
        /// <param name="endNO">验收入库结束卡号</param>
        /// <returns>符合返回true，否则返回false</returns>
        public bool IsInOrderInfoExisBoxCardNO(string strOrderId, string boxNO, string startNO, string endNO)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from ordering_cardinfo O ");
            strSql.Append(" where " + boxNO + " >=O.StartBoxNo and " + boxNO + "<=O.EndBoxNo ");
            strSql.Append(" and " + startNO + " >=O.StartNo and " + endNO + "<=O.EndNo and O.id='" + strOrderId + "' and O.Issubmit=5");
            DataTable dt = CardLogistics.DAL.DbHelperSQL.GetQueryResult(strSql.ToString());
            if (dt != null && dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
    }
}
